﻿using Lib.Data.Entitys;
using Lib.Data.Enumeration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Lib.Data; 

/// <summary>
/// 分页操作类
/// </summary>
public static class DBCommonPage {

    /// <summary>
    /// 静态构造函数
    /// </summary>
    static DBCommonPage() { }

    #region "SQL语句分页"

    /// <summary>
    /// SQL语句分页（“出错”才返回 Null，“查询结果为 0 ”返回“[]”）
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="db"></param>
    /// <param name="SQL">分页查询语句</param>
    /// <param name="cmdParams"></param>
    /// <param name="Page">页码</param>
    /// <param name="PageSize">每页记录数</param>
    /// <param name="Count">返回记录数</param>
    /// <param name="Delegate">数据行委托处理方法</param>
    /// <returns></returns>
    public static IList<T> GetCommonList<T>(this DataBase db, string SQL, IList<DbParameter> cmdParams, int Page, int PageSize, out int Count, Action<T, IDataReader> Delegate = null) {
        Count = 0;
        if (db == null) {
            return null;
        }
        SQL = db.Pagination(Page, PageSize, SQL, cmdParams, out Count);
        if (SQL == null) {
          
        } else {
            using DataReader Rows = db.ExecuteReader(SQL, cmdParams);
            if (Rows == null) {
                return null;
            }
            return Rows.ToCollection<T>(Delegate);
        }
        return null;
    }

    /// <summary>
    /// SQL语句分页（“出错”才返回 Null，“查询结果为 0 ”返回“[]”）
    /// </summary>
    /// <param name="db"></param>
    /// <param name="SQL">分页查询语句</param>
    /// <param name="cmdParams"></param>
    /// <param name="Page">页码</param>
    /// <param name="PageSize">每页记录数</param>
    /// <param name="Count">返回记录数</param>
    /// <returns></returns>
    public static DataTable GetCommonPage(this DataBase db, string SQL, IList<DbParameter> cmdParams, int Page, int PageSize, out int Count) {
        Count = 0;
        if (db == null) {
            return null;
        }
        SQL = db.Pagination(Page, PageSize, SQL, cmdParams, out Count);
        if (SQL != null) {
            return db.ExecuteDataTable(SQL, cmdParams);
        }
        return null;
    }

    /// <summary>
    /// SQL语句分页（“出错”才返回 Null，“查询结果为 0 ”返回“[]”）
    /// </summary>
    /// <param name="db"></param>
    /// <param name="SQL">分页查询语句</param>
    /// <param name="cmdParams"></param>
    /// <param name="Page">页码</param>
    /// <param name="PageSize">每页记录数</param>
    /// <param name="Count">返回记录数</param>
    /// <returns></returns>
    public static string GetSoinPage(this DataBase db, string SQL, IList<DbParameter> cmdParams, int Page, int PageSize, out int Count) {
        Count = 0;
        if (db == null) {
           return null;
        }
        SQL = db.Pagination(Page, PageSize, SQL, cmdParams, out Count);
        if (SQL != null) {
            using DataReader Rows = db.ExecuteReader(SQL, cmdParams);
            if (Rows == null) {
                return null;
            } else {
                return Common.FormatDateTime(Rows.Serialize());
            }
        }
        return null;
    }

    /// <summary>
    /// 返回处理后的SQL分页语句
    /// </summary>
    private static string Pagination(this DataBase db, int Page, int PageSize, string SQL, IList<DbParameter> cmdParams, out int Count) {
        Count = DBCommonPage.Count(db, SQL, cmdParams);
        if (Count == 0) {
            return null;
        }
        var oPage = PageEntity.Create(Page, PageSize, Count, SQL);
        oPage.Sorts = ParseSorts(SQL);
        return db.PagingKey switch {
            "Common" => CommonPaging(oPage, db.dbType),
            _ => Parse(oPage, db.dbType, db.PagingKey),
        };
    }
    /// <summary>
    /// 
    /// </summary>
    private static string CommonPaging(PageEntity e, dbType dbType) {
        var SQL = dbType switch {
            dbType.MSSQL =>
            "SELECT * FROM ({SQL}) T ORDER BY {Sorts} OFFSET {StartRowIndex} ROW FETCH NEXT {PageSize} ROWS ONLY;",
            dbType.MySQL or dbType.MariaDB =>
            "SELECT * FROM ({SQL}) T LIMIT {StartRowIndex}, {PageSize};",
            dbType.PostgreSQL =>
            "SELECT * FROM ({SQL}) T OFFSET {StartRowIndex} FETCH FIRST {PageSize} ROWS ONLY;",
            dbType.Oracle =>
            "SELECT * FROM ({SQL}) T OFFSET {StartRowIndex} ROWS FETCH NEXT {PageSize} ROWS ONLY;",
            dbType.SQLite or dbType.Excel =>
            "SELECT * FROM ({SQL}) T LIMIT {PageSize} OFFSET {StartRowIndex};",
            dbType.Access =>
            "SELECT * FROM ({SQL}) T OFFSET {StartRowIndex} ROW FETCH NEXT {PageSize} ROWS ONLY;",
            _ => null,
        };
        return SQL?
            .Replace("{SQL}", FilterSorts(e.SQL, dbType))
            .Replace("{StartRowIndex}", e.StartRowIndex.ToString())
            .Replace("{EndRowIndex}", e.EndRowIndex.ToString())
            .Replace("{PageSize}", e.PageSize.ToString())
            .Replace("{PrimaryKey}", e.PrimaryKey)
            .Replace("{Fields}", e.Fields)
            .Replace("{Table}", e.Table)
            .Replace("{Where}", e.Where)
            .Replace("{Sorts}", e.Sorts)
            ;
    }
    /// <summary>
    /// 
    /// </summary>
    private static string Parse(PageEntity e, dbType dbType, string Key) {
        var SQLs = DatabaseFactory.Connection.SQLs;
        if (SQLs.TryGetValue(dbType.ToString(), out var Page)) {
            if (Page.SQLs.TryGetValue(Key, out var SQL)) {
                return SQL
                    .Replace("{SQL}", FilterSorts(e.SQL, dbType))
                    .Replace("{StartRowIndex}", e.StartRowIndex.ToString())
                    .Replace("{EndRowIndex}", e.EndRowIndex.ToString())
                    .Replace("{PageSize}", e.PageSize.ToString())
                    .Replace("{PrimaryKey}", e.PrimaryKey)
                    .Replace("{Fields}", e.Fields)
                    .Replace("{Table}", e.Table)
                    .Replace("{Where}", e.Where)
                    .Replace("{Sorts}", e.Sorts)
                    ;
            }
        }
        return null;
    }
    /// <summary>
    /// 获取记录总数
    /// </summary>
    private static int Count(DataBase db, string SQL, IList<DbParameter> cmdParams) {
        return db.ExecuteScalar<int>($"SELECT COUNT(1) AS Count FROM ({FilterSorts(SQL,db.dbType)}) T;", cmdParams);
    }
    /// <summary>
    /// 过滤掉排序子句
    /// </summary>
    private static string FilterSorts(string SQL, dbType dbType) {
        if (dbType == dbType.MSSQL) {
            int Index = SQL.LastIndexOf(" ORDER ", StringComparison.OrdinalIgnoreCase);
            if (Index != -1 && SQL[(Index + 6)..].Contains(" BY ")) {
                if (SQL.Contains(" TOP ", StringComparison.OrdinalIgnoreCase)) {
                    SQL = SQL[..Index];
                }
            }
        }
        return SQL;
    }
    /// <summary>
    /// 解析出SQL语句的排序字段列
    /// </summary>
    private static string ParseSorts(string SQL) {
        //
        int Index = SQL.LastIndexOf(" ORDER ", StringComparison.OrdinalIgnoreCase);
        //
        var Sorts = SQL[(Index + 7)..].Trim();
        //
        if (Sorts.StartsWith("BY", StringComparison.OrdinalIgnoreCase)) {
            Sorts = Sorts[2..].Trim();
        }
        //
        Index = Sorts.IndexOf(" LIMIT ", StringComparison.OrdinalIgnoreCase);
        if (Index != -1) {
            Sorts = Sorts[..Index];
        }
        Index = Sorts.IndexOf(" OFFSET ", StringComparison.OrdinalIgnoreCase);
        if (Index != -1) {
            Sorts = Sorts[..Index];
        }
        Index = Sorts.IndexOf(")", StringComparison.OrdinalIgnoreCase);
        if (Index != -1) {
            Sorts = Sorts[..Index];
        }
        Sorts = Sorts.TrimEnd(';');
        //
        string[] Ros = Sorts.Split(',', StringSplitOptions.RemoveEmptyEntries);
        //
        var sb = new StringBuilder();
        // 
        foreach (var Rs in Ros) {
            Index = Rs.Trim().IndexOf(' ');
            if (Index != -1) {
                sb.Append($"{Rs[..Index]} {Rs[(Index+1)..].Trim().ToUpper()},");
            } else {
                sb.Append($"{Rs} ASC,");
            }
        }
        return sb.ToString().TrimEnd(',');

    }

    #endregion

}
